package com.statistics.yfdata.dao;

import com.statistics.bean.Environment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author MeowParse
 * @date 2017/9/1.
 */
@Repository
public class DataEnvironmentRepository {

	@Autowired
	@Qualifier("primaryJdbcTemplate")
	private JdbcTemplate primaryJdbcTemplate;

	@Autowired
	@Qualifier("secondaryJdbcTemplate")
	private JdbcTemplate secondaryJdbcTemplate;

	private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public List<Environment> getCount(String mac, long start, long end, String month) {

		String tableName = "history_environment_" + month;

		String sql = String.format("select max(pm25) as max_pm25,max(temperature) as max_temperature ,max(humidity) as max_humidity ," +
				"max(co2) as max_co2 ,max(noise) as max_noise,avg(pm25) as avg_pm25 ,avg(temperature) as avg_temperature ," +
				"avg(humidity) as avg_humidity ,avg(co2) as avg_co2 ,avg(noise) as avg_noise ,offset as offset" +
				" from %s where create_time >= ? and create_time < ? and mac = ? group by offset", tableName);

		RowMapper<Environment> rm = BeanPropertyRowMapper.newInstance(Environment.class);

		List<Environment> environments = secondaryJdbcTemplate.query(sql, new Object[]{start, end, mac}, rm);

		if (environments != null && environments.size() > 0) {
			return environments;
		}

		return null;

	}

	public List<Environment> getCountByWeek(String mac, long start, long end) {

		String start_time = simpleDateFormat.format(start);
		String end_time = simpleDateFormat.format(end);

		String sql = "select max(max_pm25) as max_pm25,max(max_temperature) as max_temperature ,max(max_humidity) as max_humidity ," +
				"max(max_co2) as max_co2 ,max(max_noise) as max_noise,avg(avg_pm25) as avg_pm25 ,avg(avg_temperature) as avg_temperature ," +
				"avg(avg_humidity) as avg_humidity ,avg(avg_co2) as avg_co2 ,avg(avg_noise) as avg_noise ,offset as offset" +
				" from tbl_environment_statistics_day  where statistics_time >= ? and statistics_time < ? and mac = ? group by offset";

		RowMapper<Environment> rm = BeanPropertyRowMapper.newInstance(Environment.class);

		List<Environment> environments = primaryJdbcTemplate.query(sql, new Object[]{start_time, end_time, mac}, rm);

		if (environments != null && environments.size() > 0) {
			return environments;
		}

		return null;
	}

}
